package com.amarsoft.apot.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

/**
 * 创建Excel工作簿
 */
public class WriteExcelUtils {

    private final static String EXCEL2003 = "xls";
    private final static String EXCEL2007 = "xlsx";

    /**
     *
     * @param sheetName sheet工作表名称
     * @param headList Excel第一行表头信息
     * @param dataList Excel具体数据信息
     * @param path 生成的Excel的保存路径
     * @return
     */
    public static boolean createWorkBook(String sheetName, List<String> headList, List<List<String>> dataList, String path) {

        // 创建03/07版本的工作簿
        Workbook wb = null;
        if(path.endsWith(EXCEL2003)) wb = new HSSFWorkbook(); // 03版xls
        if(path.endsWith(EXCEL2007)) {
            wb = new XSSFWorkbook();// 07版xlsx
            // new SXSSFWorkbook() => 07版xlsx行数无上限,升级版XSSFWorkbook,加快速度
        }

        //创建工作表
        Sheet sheet = wb.createSheet(sheetName != null ? sheetName : "new sheet");
        //设置字体
        Font headFont = wb.createFont();
        headFont.setFontHeightInPoints((short) 14);
        headFont.setFontName("Courier New");
        headFont.setItalic(false);
        headFont.setStrikeout(false);
        //设置头部单元格样式
        CellStyle headStyle = wb.createCellStyle();
        //设置单元格下线条及颜色
        headStyle.setBorderBottom(BorderStyle.THIN);
        headStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());
        //设置单元格左线条及颜色
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setLeftBorderColor(IndexedColors.BLUE.getIndex());
        //设置单元格又线条及颜色
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
        //设置单元格线条及颜色
        headStyle.setBorderTop(BorderStyle.THIN);
        headStyle.setTopBorderColor(IndexedColors.BLUE.getIndex());
        //设置水平对齐方式
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐方式
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headStyle.setFont(headFont);  //设置字体

        Row headRow = sheet.createRow(0);
        CreationHelper createHelper = wb.getCreationHelper();
        for (int i = 0; i < headList.size(); i++) {  //遍历表头数据
            Cell cell = headRow.createCell(i);  //创建单元格
            cell.setCellValue(createHelper.createRichTextString(headList.get(i)));  //设置值
            cell.setCellStyle(headStyle);  //设置样式
        }

        int rowIndex = 1;  //当前行索引
        //创建Rows
        //设置数据单元格格式
        CellStyle dataStyle = wb.createCellStyle();
        dataStyle.setBorderBottom(BorderStyle.THIN);  //设置单元格线条
        dataStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());   //设置单元格颜色
        dataStyle.setBorderLeft(BorderStyle.THIN);
        dataStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        dataStyle.setBorderRight(BorderStyle.THIN);
        dataStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        dataStyle.setBorderTop(BorderStyle.THIN);
        dataStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        dataStyle.setAlignment(HorizontalAlignment.LEFT);    //设置水平对齐方式
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //设置垂直对齐方式

        for (List<String> rowdata : dataList) { //遍历所有数据
            Row row = sheet.createRow(rowIndex++); //第一行为头
            for (int j = 0; j < rowdata.size(); j++) {  //编译每一行
                Cell cell = row.createCell(j);
                cell.setCellStyle(dataStyle);
                cell.setCellValue(createHelper.createRichTextString(rowdata.get(j)));
            }
        }

        /*设置列自动对齐*/
        for (int i = 0; i < headList.size(); i++) {
            sheet.autoSizeColumn(i);
        }

        try (OutputStream fileOut = new FileOutputStream(path)) {  //获取文件流
            wb.write(fileOut);   //将workbook写入文件流
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            return false;
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

}
